package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonExtBeginDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonExt;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Repository
public class PersonExtBeginDaoImpl extends BaseDaoImpl implements IPersonExtBeginDao {

@Override
public void batchSavePersonExtBegin(List<PersonExt> personExts) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
    List<Object[]> paramList = new ArrayList<>(personExts.size());
    for(PersonExt personExt : personExts){
        Object[] paramArr = {
                personExt.getId(), personExt.getCreateTime(), personExt.getUpdateTime(), personExt.getPersonId(),
                personExt.getPersonAddress(), personExt.getAgencyId(), personExt.getDeptId(), personExt.getAgencyCode(),
                personExt.getIdenNo(),personExt.getState(),//20210304新增
                personExt.getEnterAgencyDate(), personExt.getRetireDate(), personExt.getPersonTypeCode(),
                personExt.getPersonIdeCode(), personExt.getEndDate(), personExt.getDeadDate(),personExt.getRemarks(),
                personExt.getSalaCardNo(), personExt.getSalaCardBank(), personExt.getGovCredCardNo(),
                personExt.getGovCredCardBank(), personExt.getStartDate(), personExt.getMeritPayCode(),
                personExt.getSchRecCode(), personExt.getPosCode(), personExt.getGrCode(), personExt.getTecGrCode(),
                personExt.getIsAuth(), personExt.getSalaGrCode(), personExt.getPersonState(), personExt.getChangeState(),
                personExt.getVerifyState(), personExt.getPerSourceCode(), personExt.getIsSpecialPost(), personExt.getIsInsurance(),
                personExt.getIsUniSala(), personExt.getIsStopSalary(), personExt.getIsDeleted(),personExt.getMofDivCode(),
                personExt.getPersonTypeId(), personExt.getPostId(), personExt.getGradeId(), personExt.getLevelId(),personExt.getPersonName(),
                personExt.getShowOrder(),personExt.getPhone(),personExt.getPersonnelCode(),
                personExt.getFuncId(),personExt.getFuncName(),personExt.getFuncCode()
        };
        List<Object> params = new ArrayList<>(Arrays.asList(paramArr));

        paramList.add(params.toArray());
    }
//        merge
    StringBuilder sql = new StringBuilder();
    sql.append(" merge  into ").append(TableName.SAL_PERSON_EXT_BEGIN)
            .append(" a using " )
            .append( " ( select  ? id, ?  create_time, ?  update_time, ?  person_id, ?  person_address, ?  agency_id, ?  dept_id, ?  agency_code, ?  iden_no, ? state, ?  enter_agency_date," )
            .append(" ? retire_date, ?  person_type_code, ?  person_ide_code, ?  end_date, ?  dead_date, ?  remarks, ?  sala_card_no, ")
            .append(" ? sala_card_bank, ?  gov_cred_card_no, ?  gov_cred_card_bank, ?  start_date, ?  merit_pay_code, ")
            .append(" ? sch_rec_code, ?  pos_code, ?  gr_code, ?  tec_gr_code, ?  is_auth, ?  sala_gr_code, ?  person_state, ")
            .append(" ? change_state, ?  verify_state, ?  per_source_code, ?  is_special_post, ?  is_insurance, ?  is_uni_sala, ?  is_stop_salary, ?  is_deleted, ? mof_div_code, ")
            .append(" ? person_type_id, ?  post_id, ?  grade_id, ?  level_id, ? person_name, ? show_order, ? phone, ? personnel_code, ? func_id, ? func_name, ? func_code from dual ) b  ")
            .append(" on (a.person_id = b.person_id) " )
            .append(" WHEN MATCHED THEN " )
            .append("   UPDATE SET  a.create_time=b.create_time, a.update_time=b.update_time, a.person_address=b.person_address, a.agency_id=b.agency_id,  " )
            .append(" a.dept_id=b.dept_id, a.agency_code=b.agency_code, a.iden_no=b.iden_no,a.state=b.state, a.enter_agency_date=b.enter_agency_date,a.retire_date=b.retire_date,  ")
            .append(" a.person_type_code=b.person_type_code,a.person_ide_code=b.person_ide_code,a.end_date=b.end_date,a.dead_date=b.dead_date,a.remarks=b.remarks,  ")
            .append(" a.sala_card_no=b.sala_card_no,a.sala_card_bank=b.sala_card_bank,a.gov_cred_card_no=b.gov_cred_card_no,a.gov_cred_card_bank=b.gov_cred_card_bank,  ")
            .append(" a.start_date=b.start_date,a.merit_pay_code=b.merit_pay_code,a.sch_rec_code=b.sch_rec_code,a.pos_code=b.pos_code,a.gr_code=b.gr_code,a.tec_gr_code=b.tec_gr_code, ")
            .append(" a.is_auth=b.is_auth,a.sala_gr_code=b.sala_gr_code,a.person_state=b.person_state,a.change_state=b.change_state,a.verify_state=b.verify_state,a.per_source_code=b.per_source_code,  ")
            .append(" a.is_special_post=b.is_special_post,a.is_insurance=b.is_insurance,a.is_uni_sala=b.is_uni_sala,a.is_stop_salary=b.is_stop_salary,a.is_deleted=b.is_deleted,a.mof_div_code=b.mof_div_code, ")
            .append(" a.person_type_id=b.person_type_id,a.post_id=b.post_id,a.grade_id=b.grade_id,a.level_id=b.level_id,a.person_name=b.person_name,a.show_order=b.show_order,a.phone=b.phone,a.personnel_code=b.personnel_code,a.func_id=b.func_id,a.func_name=b.func_name,a.func_code=b.func_code ")
            .append( " WHEN NOT MATCHED THEN " )
            .append( "   INSERT " )
            .append( "    (a.id, a.create_time, a.update_time, a.person_id, a.person_address, a.agency_id, a.dept_id, a.agency_code, a.iden_no,a.state, a.enter_agency_date, ")
            .append(" a.retire_date, a.person_type_code, a.person_ide_code, a.end_date, a.dead_date, a.remarks, a.sala_card_no, ")
            .append(" a.sala_card_bank, a.gov_cred_card_no, a.gov_cred_card_bank, a.start_date, a.merit_pay_code, ")
            .append(" a.sch_rec_code, a.pos_code, a.gr_code, a.tec_gr_code, a.is_auth, a.sala_gr_code, a.person_state, ")
            .append(" a.change_state, a.verify_state, a.per_source_code, a.is_special_post, a.is_insurance, a.is_uni_sala, a.is_stop_salary, a.is_deleted,a.mof_div_code, ")
            .append(" a.person_type_id, a.post_id, a.grade_id, a.level_id,a.person_name,a.show_order,a.phone,a.personnel_code,a.func_id,a.func_name,a.func_code )")
            .append( "   VALUES " )
             .append( "    (b.id, b.create_time, b.update_time, b.person_id, b.person_address, b.agency_id, b.dept_id, b.agency_code, b.iden_no,b.state, b.enter_agency_date, ")
            .append(" b.retire_date, b.person_type_code, b.person_ide_code, b.end_date, b.dead_date, b.remarks, b.sala_card_no, ")
            .append(" b.sala_card_bank, b.gov_cred_card_no, b.gov_cred_card_bank, b.start_date, b.merit_pay_code, ")
            .append(" b.sch_rec_code, b.pos_code, b.gr_code, b.tec_gr_code, b.is_auth, b.sala_gr_code, b.person_state, ")
            .append(" b.change_state, b.verify_state, b.per_source_code, b.is_special_post, b.is_insurance, b.is_uni_sala, b.is_stop_salary, b.is_deleted,b.mof_div_code, ")
            .append(" b.person_type_id, b.post_id, b.grade_id, b.level_id,b.person_name,b.show_order,b.phone,b.personnel_code,b.func_id,b.func_name,b.func_code )");

    super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
        public int getBatchSize() {
            return paramList.size();
            //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
        }
        public void setValues(PreparedStatement ps, int i)throws SQLException {
            Object[] sme = paramList.get(i);
            for(int a = 0; a < 50; a++){
                ps.setObject(a+1,sme[a]);
            }

        }
    });
}
    @Override
    public void batchPhysicalDeletePersonExtBegin(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.SAL_PERSON_EXT_BEGIN)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        super.getjdbcTemplate().update(sb.toString(), province);
    }

    @Override
    public List<PersonExt> queryPersonExt(String mofDivCode,List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        if(!CollectionUtils.isEmpty(agencyIds)) {
            sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary,mof_div_code,is_deleted," )
                    .append("person_name,show_order,phone,personnel_code,func_id,func_name,func_code ");
            sql.append(" from ").append(TableName.SAL_PERSON_EXT_BEGIN)
                    .append(" where is_deleted=0  ").append(" and ").append(SysUtils.inSql("agency_id", agencyIds))
                    .append(" and mof_div_code=? ");
            sql.append(" order by create_time");
            Object[] param = {mofDivCode};
            //请求参数初始化
            return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonExt.class),param);
        }
        return new ArrayList<>();
    }

    @Override
    public void updatePersonExtBeginCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SAL_PERSON_EXT_BEGIN);
        sql.append(" set  agency_code=?   where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify personExtBegin : updateList) {
            Object[] params = new Object[]{
                    personExtBegin.getAgencyCode(),personExtBegin.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
}
